﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using FX.CC.Andon.Entity;
using FX.CC.Andon.Entity.SystemManage;
using FX.CC.Andon.Utility.Common;
using FX.CC.Andon.Utility.DataBase;

namespace FX.CC.Andon.Service.SystemManage
{
    public class NoticeDAL : DbHelperBase
    {
        public int GetTotal()
        {
            var strSql = new StringBuilder();
            strSql.Append("select  count(*) AS [Total] FROM UT_Notice");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<NoticeEntity> GetNoticeList(Pagination qcmodel)
        {
            var list = new List<NoticeEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by InputTime desc ) AS idx,NoticeId,NoticeTitle,InputTime,Flag,AddUser FROM UT_Notice ");
            strSql.Append(" WHERE 1=1 ");
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new NoticeEntity();
                            if (reader["NoticeId"] != DBNull.Value)
                                model.NoticeId = new Guid(reader["NoticeId"].ToString());
                            model.NoticeTitle = reader["NoticeTitle"] == obj ? null : Convert.ToString(reader["NoticeTitle"]);
                            model.AddUser = reader["AddUser"] == obj ? null : Convert.ToString(reader["AddUser"]);
                            if (reader["Flag"] != DBNull.Value)
                                model.Flag = (bool)reader["Flag"];
                            if (reader["InputTime"] != DBNull.Value)
                                model.InputTime = Convert.ToDateTime(reader["InputTime"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool Insert(NoticeEntity model)
        {
            bool result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [UT_Notice]([NoticeId],[NoticeTitle],[Flag],[AddUser]) ");
            strSql.Append(" VALUES(@NoticeId,@NoticeTitle,@Flag,@AddUser) ");
            using (DbCommand cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@NoticeId", model.NoticeId);
                    Db.AddInParameter(cmd, "@NoticeTitle", model.NoticeTitle);
                    Db.AddInParameter(cmd, "@Flag", model.Flag);
                    Db.AddInParameter(cmd, "@AddUser", model.AddUser);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool Modify(NoticeEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [UT_Notice] SET [NoticeTitle] = @NoticeTitle,[Flag]=@Flag,[AddUser] = @AddUser ");
            strSql.Append(" WHERE [NoticeId] = @NoticeId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@NoticeId", model.NoticeId);
                    Db.AddInParameter(cmd, "@NoticeTitle", model.NoticeTitle);
                    Db.AddInParameter(cmd, "@Flag", model.Flag);
                    Db.AddInParameter(cmd, "@AddUser", model.AddUser);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public NoticeEntity GetNoticeInfo(string id)
        {
            var model = new NoticeEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [NoticeId],[NoticeTitle],[InputTime],[Flag],AddUser FROM [UT_Notice] where NoticeId=@NoticeId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                Db.AddInParameter(cmd, "@NoticeId", id);
                try
                {
                    using (var reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["NoticeId"] != DBNull.Value)
                                model.NoticeId = new Guid(reader["NoticeId"].ToString());
                            model.NoticeTitle = reader["NoticeTitle"] == obj ? null : Convert.ToString(reader["NoticeTitle"]);
                            if (reader["InputTime"] != DBNull.Value)
                                model.InputTime = Convert.ToDateTime(reader["InputTime"]);
                            if (reader["Flag"] != DBNull.Value)
                                model.Flag = Convert.ToBoolean(reader["Flag"]);
                            model.AddUser = reader["AddUser"] == obj ? null : Convert.ToString(reader["AddUser"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public bool RemoveNotice(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [UT_Notice] WHERE NoticeId=@NoticeId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@NoticeId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public List<NoticeEntity> ViewTxtList()
        {
            var list = new List<NoticeEntity>();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT * FROM [UT_Notice] where Flag=1  ORDER BY InputTime desc ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new NoticeEntity();
                            model.NoticeTitle = reader["NoticeTitle"] == obj ? null : Convert.ToString(reader["NoticeTitle"]);
                            if (reader["InputTime"] != DBNull.Value)
                                model.InputTime = Convert.ToDateTime(reader["InputTime"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return list;
            }
        }
    }
}
